<?php  
	$WaiXingChiCun = null;
	$ChanPinXiLie = null;
	$ChanPinLeiXing=null;
	$ChanPinXingHao=null;
	
	$dbName = "sqlsrv:Server=172.31.3.9,1433;Database=UFDATA_001_2017";   //emc3030
	$dbUser = "sa";    //
	$dbPassword = "TDled2017";    //
	$db = new PDO($dbName, $dbUser, $dbPassword);   //這裡是PDO鏈接方法

	$conn=mysql_connect("127.0.0.1:3306","root","tdled2018");//這裡是MySQL鏈接方法
	if (!$conn){
		die('Could not connect: ' . mysql_error());
	}
	//echo "conn".date("Y-m-j H:i:s",time()). "<br>";//觀看每次時間
	mysql_select_db("tddb", $conn);

	mysql_query("set character set 'utf8'");//读库 

	mysql_query("set names 'utf8'");//写库 
	// 0工序,1日期,2班別,3機台號,4工單號,5計劃數量,6計劃時間
	
	//每次保存，就Delete
	if ($_FILES["csv"]["size"] > 0) {//如果csv有數據就執行以下語句
		//get the csv file得到csv的
		$file = $_FILES["csv"]["tmp_name"]; 
		$handle = fopen($file,"r"); 
		//寫入表身，補關連欄位，
		//loop through the csv file and insert into database 
		$data = NULL;
		do {
			// CSV的Excel支持GBK编码，一定要转换，否则乱码 
			$GongXu = iconv('gb2312','utf-8',$data[0]); //iconv('utf-8', 'gbk', $data[0]); 
			$JiTai = iconv('gb2312','utf-8',$data[3]);
			//echo $data[0];//.",".$data[1]).",".$data[2]).",".$data[3]).",".$data[4]).",".$data[5]).",".$data[6]);
			//select pd 型號 from $data[0]) where wo   mom 型號 補欄位，
			//select cpss 產品系列from     where pd  
			//3030
			//EMC
			//pt 產品類型 from     where pd 3C
			//寫入表身，補關連欄位， 
			//INSERT INTO `m_pl_temp`(`GongXu`, `RiQi`, `BanBie`, `JiTai`, `GongDan`, `JiHuaQTY`, `JiHuaTime`) VALUES ([value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8]),([value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8])
			$sql = "select InvCode, OrderCode from mom_orderdetail where MoLotCode='".$data[4]."' AND RelsDate<>''";  //2018-3-12 10:49:59注釋:用工單號從庫里取出產品型號和訂單號,審核日期不等於空白.
			//echo($sql)."</br>";
			$res = $db->query($sql);
			$InvCode = null;
			while ($row = $res->fetch()){
				$ChanPinXingHao=$row['InvCode'];//成品型號
				$DingDanHao=$row['OrderCode'];//訂單號
			}
			$sz = substr($ChanPinXingHao,1,2); //成品型號 ，第2位，截2碼
			$BuChongMa = substr($ChanPinXingHao,8,1);//成品型號 ，第9位，截1碼
			//2018-3-9 16:27:52注釋:備份原語句,採用聯查補充碼形式:$sql = "select WaiXingChiCun, ChanPinLeiXing FROM `waixingdaima` WHERE`waixingdaima` ='".$sz."' ";
			//取出外形尺寸和補充碼
			$sql = 
				"SELECT 	
					`WaiXingChiCun`, `ChanPinLeiXing`, `XiLieQianZhuiMa` 
				FROM 	
					`waixingdaima`,`buchongma` 
				WHERE 	
					`WaiXingDaiMa` ='".$sz."'
				AND	
					`BuChongMa` ='".$BuChongMa."'
				"
			;	

			$result = mysql_query($sql);
			
			//2018-3-8 18:40:50注釋:先搜尋補充碼,補充碼有的就直接截取補充碼表的前綴碼,如果找不到就一定是工單的第二碼.
			if (mysql_num_rows($result) < 1){
				//如果結果集行數小於1,那麼就是找不到補充碼,直接用原來的工單號第二碼形式
				$sql = 
					"SELECT 	
						WaiXingChiCun, ChanPinLeiXing 
					FROM 	
						`waixingdaima`
					WHERE 	
						`waixingdaima` ='".$sz."' 
					"
				;	
				$result = mysql_query($sql);
				
				while ($row = mysql_fetch_array($result)){
					$WaiXingChiCun=$row['WaiXingChiCun'];
					$ChanPinLeiXing=$row['ChanPinLeiXing'];
				}
				$pre=substr($data[4],1,1);//前綴碼=工單號,第二位,截1碼(這裡需要區分常規或客製化工單號)
				
			}else{
				while ($row = mysql_fetch_array($result)){
					$WaiXingChiCun=$row['WaiXingChiCun'];
					$ChanPinLeiXing=$row['ChanPinLeiXing'];
					$pre=$row['XiLieQianZhuiMa'];
				}
			}
			
			if ($pre == "K") { $pre ="SSC";}
			$ChanPinXiLie =$pre.$WaiXingChiCun; 
			
			if ($data[0]) {
			   mysql_query("INSERT INTO `m_pl_temp`(`GongXu`, `RiQi`, `BanBie`, `JiTai`, `DingDanHao`, `GongDan`, `JiHuaQTY`, `JiHuaTime`, `ChanPinXingHao`, `ChanPinLeiXing`, `ChanPinXiLie`) VALUES
				   ( 
					'".$GongXu."', '".$data[1]."','".$data[2]."','".$JiTai."','".$DingDanHao."','".$data[4]."','".$data[5]."','".$data[6]."','".$ChanPinXingHao."','".$ChanPinLeiXing."','".$ChanPinXiLie."'
				   )
			   ");
			
			}
		} while ($data = fgetcsv($handle,100000,",","'"));
		
		$sql = 
			"INSERT INTO 		
				`m_d_temp`(	
					`GongXu`,
					`RiQi`,
					`BanBie`,
					`ChanPinXingHao`,
					`ChanPinLeiXing`,
					`JiTai`,
					`DingDanHao`,
					`GongDan`,
					`ChanPinXiLie`,
					`JiHuaQTY`,
					`JiHuaTime`
				)	
			SELECT 		
				`GongXu`,	
				`RiQi`,	
				`BanBie`,	
				`ChanPinXingHao`,	
				`ChanPinLeiXing`,	
				`JiTai`,	
				`DingDanHao`,
				`GongDan`,	
				`ChanPinXiLie`,	
				`JiHuaQTY`,	
				`JiHuaTime`	
			From 		
				`m_pl_temp`	
			"
		;		
		//echo($sql)."</br>";
		$result = mysql_query($sql);//執行表複製
		//echo($result)."</br>";
		// 

		//redirect 
		//header('Location: import.php?success=1'); die; 

	} 
// '".addslashes($data[0])."', 
// '".addslashes($data[1])."', 
// '".addslashes($data[2])."', 
// '".addslashes($data[3])."', 
// '".addslashes($data[4])."', 
// '".addslashes($data[5])."', 
// '".addslashes($data[6])."'
?> 





